/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nanterre.annuaire.bd;

import com.nanterre.annuaire.model.*;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author illamhat
 */
public class BDInteraction {

    public static final Oracle Oracle = new Oracle();

    public BDInteraction() {
    }
    
    public float getCreditsUE(int idUE){
        String procedure = "{? = call nb_credit_ue (?)}";
        CallableStatement statement;
        try {
            Oracle.logIn();
            statement = Oracle.getConn().prepareCall(procedure);
            statement.registerOutParameter(1, java.sql.Types.NUMERIC);
            statement.setInt(2, idUE);
            statement.execute();
            return statement.getFloat(1);
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }

    public Domaine getDomaineById(int id){
        try {
            Domaine domaine = null;
            Oracle.logIn();
            ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DOMAINE WHERE ID_DOM=" + id);
            while (rs.next()) {
                domaine = new Domaine();
                domaine.setId(rs.getInt("ID_DOM"));
                domaine.setCodeApogee(rs.getString("CODE_APOGEE"));
                domaine.setParentID(-1);
                domaine.setLibelleComplet(rs.getString("LIBELLE_DOMAINE"));
                domaine.setDateCreation(rs.getDate("D_CREATION"));
                domaine.setDateModification(rs.getDate("D_MODIFICATION"));
                domaine.setLibelleCourt(null);
                domaine.setType(TypeElement.DOMAINE);

            }
            Oracle.logOut();
            return domaine;
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Domaine getDomaineBylibelle(String libelle)  {
        try {
            Domaine domaine = null;
            Oracle.logIn();
            ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DOMAINE WHERE LIBELLE_DOMAINE='" + libelle + "'");
            while (rs.next()) {
                domaine = new Domaine();
                domaine.setId(rs.getInt("ID_DOM"));
                domaine.setCodeApogee(rs.getString("CODE_APOGEE"));
                domaine.setParentID(-1);
                domaine.setLibelleComplet(rs.getString("LIBELLE_DOMAINE"));
                domaine.setDateCreation(rs.getDate("D_CREATION"));
                domaine.setDateModification(rs.getDate("D_MODIFICATION"));
                domaine.setLibelleCourt(null);
                domaine.setType(TypeElement.DOMAINE);

            }
            Oracle.logOut();
            return domaine;
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Domaine getDomaineByApogee(String codeApogee) {
        try {
            Domaine domaine = null;
            Oracle.logIn();
            ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DOMAINE WHERE CODE_APOGEE='" + codeApogee + "'");
            while (rs.next()) {
                domaine = new Domaine();
                domaine.setId(rs.getInt("ID_DOM"));
                domaine.setCodeApogee(rs.getString("CODE_APOGEE"));
                domaine.setParentID(-1);
                domaine.setLibelleComplet(rs.getString("LIBELLE_DOMAINE"));
                domaine.setDateCreation(rs.getDate("D_CREATION"));
                domaine.setDateModification(rs.getDate("D_MODIFICATION"));
                domaine.setLibelleCourt(null);
                domaine.setType(TypeElement.DOMAINE);

            }
            Oracle.logOut();
            return domaine;
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<Domaine> getAllDomaines() {
        List<Domaine> domaines = new ArrayList<Domaine>();
        try {
            Domaine domaine = null;
            
            Oracle.logIn();
            ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DOMAINE");
            while (rs.next()) {
                domaine = new Domaine();
                domaine.setId(rs.getInt("ID_DOM"));
                domaine.setCodeApogee(rs.getString("CODE_APOGEE"));
                domaine.setParentID(-1);
                domaine.setLibelleComplet(rs.getString("LIBELLE_DOMAINE"));
                domaine.setDateCreation(rs.getDate("D_CREATION"));
                domaine.setDateModification(rs.getDate("D_MODIFICATION"));
                domaine.setLibelleCourt(null);
                domaine.setType(TypeElement.DOMAINE);

                domaines.add(domaine);

            }
            Oracle.logOut();
            
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return domaines;
    }

    public Composante getComposanteById(int id) {
        Composante composante = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM COMPOSANTE WHERE ID_COMP=" + id);
        try {
            while (rs.next()) {
                composante = new Composante();
                composante.setId(rs.getInt("ID_COMP"));
                composante.setCodeApogee(rs.getString("CODE_APOGEE"));
                composante.setParentID(-1);
                composante.setLibelleComplet(rs.getString("LIBELLE_LONG"));
                composante.setDateCreation(rs.getDate("D_CREATION"));
                composante.setDateModification(rs.getDate("D_MODIFICATION"));
                composante.setLibelleCourt(rs.getString("LIBELLE_COURT"));
                composante.setType(TypeElement.COMPOSANTE);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return composante;
    }

    public Composante getComposanteByLibelleCourt(String libelleCourt) {
        Composante composante = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM COMPOSANTE WHERE LIBELLE_COURT='" + libelleCourt + "'");
        try {
            while (rs.next()) {
                composante = new Composante();
                composante.setId(rs.getInt("ID_COMP"));
                composante.setCodeApogee(rs.getString("CODE_APOGEE"));
                composante.setParentID(-1);
                composante.setLibelleComplet(rs.getString("LIBELLE_LONG"));
                composante.setDateCreation(rs.getDate("D_CREATION"));
                composante.setDateModification(rs.getDate("D_MODIFICATION"));
                composante.setLibelleCourt(rs.getString("LIBELLE_COURT"));
                composante.setType(TypeElement.COMPOSANTE);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return composante;
    }

    public Composante getComposanteByLibelleLong(String libelleLong) {
        Composante composante = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM COMPOSANTE WHERE LIBELLE_LONG='" + libelleLong + "'");
        try {
            while (rs.next()) {
                composante = new Composante();
                composante.setId(rs.getInt("ID_COMP"));
                composante.setCodeApogee(rs.getString("CODE_APOGEE"));
                composante.setParentID(-1);
                composante.setLibelleComplet(rs.getString("LIBELLE_LONG"));
                composante.setDateCreation(rs.getDate("D_CREATION"));
                composante.setDateModification(rs.getDate("D_MODIFICATION"));
                composante.setLibelleCourt(rs.getString("LIBELLE_COURT"));
                composante.setType(TypeElement.COMPOSANTE);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return composante;
    }

    public Composante getComposanteByApogee(String codeApogee)  {
        Composante composante = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM COMPOSANTE WHERE CODE_APOGEE='" + codeApogee + "'");
        try {
            while (rs.next()) {
                composante = new Composante();
                composante.setId(rs.getInt("ID_COMP"));
                composante.setCodeApogee(rs.getString("CODE_APOGEE"));
                composante.setParentID(-1);
                composante.setLibelleComplet(rs.getString("LIBELLE_LONG"));
                composante.setDateCreation(rs.getDate("D_CREATION"));
                composante.setDateModification(rs.getDate("D_MODIFICATION"));
                composante.setLibelleCourt(rs.getString("LIBELLE_COURT"));
                composante.setType(TypeElement.COMPOSANTE);

            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return composante;
    }

    public List<Composante> getAllComposantes() {
        Composante composante = null;
        List<Composante> composantes = new ArrayList<Composante>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from composante where id_comp in (select distinct(FID_COMP) from diplome)");
        try {
            while (rs.next()) {
                composante = new Composante();
                composante.setId(rs.getInt("ID_COMP"));
                composante.setCodeApogee(rs.getString("CODE_APOGEE"));
                composante.setParentID(-1);
                composante.setLibelleComplet(rs.getString("LIBELLE_LONG"));
                composante.setDateCreation(rs.getDate("D_CREATION"));
                composante.setDateModification(rs.getDate("D_MODIFICATION"));
                composante.setLibelleCourt(rs.getString("LIBELLE_COURT"));
                composante.setType(TypeElement.COMPOSANTE);

                composantes.add(composante);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return composantes;
    }

    public List<Mention> getAllMentions() {
        Mention mention = null;
        List<Mention> mentions = new ArrayList<Mention>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM MENTION");
        try {
            while (rs.next()) {
                mention = new Mention();
                mention.setId(rs.getInt("ID_MEN"));
                mention.setCodeApogee(rs.getString("CODE_APOGEE_LMD2"));
                mention.setLibelleComplet(rs.getString("LIBELLE_MENTION"));
                mention.setDateCreation(rs.getDate("D_CREATION"));
                mention.setDateCreation(rs.getDate("D_MODIFICATION"));
                mention.setType(TypeElement.MENTION);

                mentions.add(mention);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return mentions;
    }

    public List<Mention> getAllMentionsByIdDomaine(int idDomaine)  {
        Mention mention = null;
        List<Mention> mentions = new ArrayList<Mention>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM MENTION WHERE FID_DOM=" + idDomaine);
        try {
            while (rs.next()) {
                mention = new Mention();
                mention.setId(rs.getInt("ID_MEN"));
                mention.setCodeApogee(rs.getString("CODE_APOGEE_LMD2"));
                mention.setLibelleComplet(rs.getString("LIBELLE_MENTION"));
                mention.setDateCreation(rs.getDate("D_CREATION"));
                mention.setDateCreation(rs.getDate("D_MODIFICATION"));
                mention.setType(TypeElement.MENTION);

                mentions.add(mention);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return mentions;
    }

    public Mention getMentionsByIdMention(int idMention) {
        Mention mention = new Mention();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM MENTION WHERE ID_MEN=" + idMention);
        try {
            while (rs.next()) {

                mention.setId(rs.getInt("ID_MEN"));
                mention.setCodeApogee(rs.getString("CODE_APOGEE_LMD2"));
                mention.setLibelleComplet(rs.getString("LIBELLE_MENTION"));
                mention.setDateCreation(rs.getDate("D_CREATION"));
                mention.setDateCreation(rs.getDate("D_MODIFICATION"));
                mention.setType(TypeElement.MENTION);


            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return mention;
    }

    public List<Diplome> getAllDiplomes() {
        Diplome diplome = null;
        List<Diplome> diplomes = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DIPLOME WHERE OUVERT=1 AND fid_type_dip <= 2  And id_dip in (select code_dip from apogee_liaison_parcours) order by LIBELLE_DIPLOME");
        try {
            while (rs.next()) {
                diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                diplomes.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return diplomes;
    }

    public List<Diplome> getAllDiplomesByComposante(int fidComp) {
        Diplome diplome = null;
        List<Diplome> diplomes = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT d.*, p.NOM, p.PRENOM FROM DIPLOME d, PERSONNEl p WHERE d.OUVERT=1 AND d.FID_TYPE_DIP < 3 AND d.FID_COMP = " + fidComp + " and d.FID_RESP = p.ID_PERS");
        try {
            while (rs.next()) {
                diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setNomResponsable(rs.getString("NOM"));
                diplome.setPrenomResponsable(rs.getString("PRENOM"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                diplomes.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return diplomes;
    }

    public List<Diplome> getAllDiplomesByIdMention(int idMention) {
        Diplome diplome = null;
        List<Diplome> diplomes = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DIPLOME WHERE OUVERT=1 AND fid_type_dip <= 2 AND FID_MEN=" + idMention + " And id_dip in (select code_dip from apogee_liaison_parcours) order by LIBELLE_DIPLOME");
        try {
            while (rs.next()) {
                diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                if (diplome.getFidResponsable() > 0) {
                    ResultSet rs2 = Oracle.doSelectRequest("select NOM, PRENOM from PERSONNEL where ID_PERS = " + diplome.getFidResponsable());
                    while (rs2.next()) {
                        diplome.setNomResponsable(rs2.getString("NOM"));
                        diplome.setPrenomResponsable(rs2.getString("PRENOM"));
                    }
                }
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                diplomes.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return diplomes;
    }

    public List<Diplome> getAllDiplomesByIdDomaine(int idDomaine) {
        Diplome diplome = null;
        List<Diplome> diplomes = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * FROM DIPLOME "
                + "WHERE OUVERT = 1 "
                + "AND FID_MEN IN (SELECT ID_MEN FROM MENTION WHERE FID_DOM=" + idDomaine + ") "
                + "order by LIBELLE_DIPLOME");
        try {
            while (rs.next()) {
                diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                diplomes.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return diplomes;
    }

    public Diplome getDiplomeById(int diplomeID) {
        Diplome diplome = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select d.*, a.code_dip_apo from diplome d, apogee_liaison_parcours a where d.id_dip = " + diplomeID
                + " and d.id_dip = a.code_dip");
        try {
            while (rs.next()) {
                diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setCodeApogee(rs.getString("code_dip_apo"));
                diplome.setType(TypeElement.DIPLOME);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return diplome;
    }

    public List<Diplome> getDiplomeByLibelleUE(String libelle) {
        List<Diplome> liste = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from diplome where id_dip in "
                + "(select fid_diplome from specialite where id_specialite in "
                + "(select fid_specialite from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP where libelle = '" + libelle + "' and TABLE_SOURCE = 'UE'))) and FID_TYPE_DIP < 3 and OUVERT=1");
        try {
            while (rs.next()) {
                Diplome diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                liste.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return liste;
    }

    public List<Diplome> getDiplomeByLibelleEC(String libelleEC) {
        List<Diplome> liste = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from diplome where id_dip in (select fid_diplome from specialite where id_specialite in"
                + "(select fid_specialite from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP where libelle = '" + libelleEC + "' and TABLE_SOURCE = 'EC'))) "
                + "and OUVERT=1");
        try {
            while (rs.next()) {
                Diplome diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                liste.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return liste;
    }

    public Diplome getDiplomeByParcoursID(int parcoursID) {
        Oracle.logIn();
        Diplome diplome = null;
        ResultSet rs = Oracle.doSelectRequest("select * from DIPLOME where ID_DIP in (select FID_DIPLOME from SPECIALITE where ID_SPECIALITE in (select FID_SPECIALITE from PARCOURS where ID_PARCOURS = " + parcoursID + "))");
        try {
            while (rs.next()) {
                diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return diplome;
    }

    public List<Parcours> getAllParcoursByParentId(int parentID){
        Parcours parcours = null;
        List<Parcours> listParcours = new ArrayList<Parcours>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select p.* from parcours p, apogee_liaison_parcours ap where p.fid_specialite in (select id_specialite from specialite where fid_diplome = "+parentID+") and ap.code_parcours = p.id_parcours order by annee");
        try {
            while (rs.next()) {
                parcours = new Parcours();
                parcours.setId(rs.getInt("ID_PARCOURS"));
                parcours.setFidSpecialite(rs.getInt("FID_SPECIALITE"));
                parcours.setLibelleComplet(rs.getString("LIBELLE_PARCOURS"));
                parcours.setType(TypeElement.PARCOURS);

                listParcours.add(parcours);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return listParcours;
    }

    public Parcours getParcoursById(int parcoursID) {
        Oracle.logIn();
        Parcours parcours = null;
        ResultSet rs = Oracle.doSelectRequest("select * from PARCOURS where ID_PARCOURS = " + parcoursID);
        try {
            while (rs.next()) {
                parcours = new Parcours();
                parcours.setId(rs.getInt("ID_PARCOURS"));
                parcours.setFidSpecialite(rs.getInt("FID_SPECIALITE"));
                parcours.setLibelleComplet(rs.getString("LIBELLE_PARCOURS"));
                parcours.setType(TypeElement.PARCOURS);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return parcours;
    }

    public List<Integer> getParcoursIdByUeId(int ueId) {
        List<Integer> resultat = new ArrayList<Integer>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select CODE_PARCOURS from APOGEE_CORR_ELP where TABLE_SOURCE = 'UE' and ID_SOURCE = " + ueId);
        try {
            while (rs.next()) {
                resultat.add(rs.getInt("CODE_PARCOURS"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return resultat;
    }

    public List<UE> getAllUeByParentId(int parentID)  {
        UE ue = null;
        List<UE> listUE = new ArrayList<UE>();
        Oracle.logIn();
        
/*          select ap.*, s.num_listeue from UE u, TYPE_UE t, APOGEE_CORR_ELP ap, semestre_ue s where u.id_ue = ap.id_source and ap.CODE_PARCOURS = 4767 and ap.TABLE_SOURCE = 'UE'
            and u.fid_type_ue = t.id_type and s.fid_ue = ap.id_source and s.fid_parcours = ap.CODE_PARCOURS
            order by ap.num_semestre
 */
        
//        ResultSet rs = Oracle.doSelectRequest("select * from semestre_ue where fid_parcours = "+parentID);
        ResultSet rs = Oracle.doSelectRequest("select ap.*, s.num_listeue from UE u, TYPE_UE t, APOGEE_CORR_ELP ap, semestre_ue s where u.id_ue = ap.id_source and ap.CODE_PARCOURS = "+parentID+" and ap.TABLE_SOURCE = 'UE' "
            +"and u.fid_type_ue = t.id_type and s.fid_ue = ap.id_source and s.fid_parcours = ap.CODE_PARCOURS "
            +"order by ap.num_semestre");
        try {
            while (rs.next()) {
                ue = new UE();
                ue.setId(rs.getInt("ID_SOURCE"));
                ue.setType(TypeElement.UE);
                ue.setNumListeUe(rs.getInt("NUM_LISTEUE"));
//                ResultSet rs2 = Oracle.doSelectRequest("select ap.* from UE u, TYPE_UE t, APOGEE_CORR_ELP ap where u.id_ue = ap.id_source and ap.id_source = "+ue.getId()+" and ap.CODE_PARCOURS = " + parentID + " and ap.TABLE_SOURCE = 'UE'"
//                    + " and u.fid_type_ue = t.id_type"
//                    + " order by num_semestre");
//                while (rs2.next()) {
                ue.setSemestre(rs.getInt("NUM_SEMESTRE"));
                ue.setParentID(rs.getInt("CODE_PARCOURS"));
                ue.setLibelleComplet(rs.getString("LIBELLE"));
                ue.setCodeApogee(rs.getString("COD_APO"));
                ue.setLibelleCourt(rs.getString("LIB_COURT"));
                ResultSet rs2 = Oracle.doSelectRequest("SELECT * from SEMESTRE_UE WHERE FID_PARCOURS = " + parentID + " and FID_UE = " + ue.getId() + " and NUM_SEMESTRE = " + ue.getSemestre());
                while (rs2.next()) {
                    ue.setNumListeUe(rs2.getInt("NUM_LISTEUE"));
                }
//                }
                listUE.add(ue);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return listUE;
    }

    public List<UE> getUeByParentIdAndLibelleUE(int parentID, String libelleUE) {
        UE ue = null;
        List<UE> listUE = new ArrayList<UE>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * from APOGEE_CORR_ELP WHERE CODE_PARCOURS = " + parentID + ""
                + " and TABLE_SOURCE = 'UE' "
                + "and (LIBELLE = '" + libelleUE + "' or LIB_LONG = '" + libelleUE + "') order by num_semestre");
        try {
            while (rs.next()) {
                ue = new UE();
                ue.setId(rs.getInt("ID_SOURCE"));
                ue.setSemestre(rs.getInt("NUM_SEMESTRE"));
                ue.setParentID(rs.getInt("CODE_PARCOURS"));
                ue.setLibelleComplet(rs.getString("LIBELLE"));
                ue.setCodeApogee(rs.getString("COD_APO"));
                ue.setLibelleCourt(rs.getString("LIB_COURT"));
                ue.setType(TypeElement.UE);

                listUE.add(ue);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return listUE;
    }
    
    

    public UE getUeById(String codeAppoge) {
        UE ue = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from apogee_corr_elp where cod_apo = '"+codeAppoge+"' and table_source = 'UE'");
        try {
            while (rs.next()) {
                ue = new UE();
                ue.setId(rs.getInt("ID_SOURCE"));
                ue.setSemestre(rs.getInt("NUM_SEMESTRE"));
                ue.setParentID(rs.getInt("CODE_PARCOURS"));
                ue.setLibelleComplet(rs.getString("LIBELLE"));
                ue.setCodeApogee(rs.getString("COD_APO"));
                ue.setLibelleCourt(rs.getString("LIB_COURT"));
                ue.setType(TypeElement.UE);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return ue;
    }

    public List<UE> getUeByParentIdAndApogeeUE(int parentID, String codeApogeeUE) {
        UE ue = null;
        List<UE> listUE = new ArrayList<UE>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT * from APOGEE_CORR_ELP WHERE CODE_PARCOURS = " + parentID + ""
                + " and TABLE_SOURCE = 'UE' "
                + "and COD_APO = '" + codeApogeeUE + "' order by num_semestre");
        try {
            while (rs.next()) {
                ue = new UE();
                ue.setId(rs.getInt("ID_SOURCE"));
                ue.setSemestre(rs.getInt("NUM_SEMESTRE"));
                ue.setParentID(rs.getInt("CODE_PARCOURS"));
                ue.setLibelleComplet(rs.getString("LIBELLE"));
                ue.setCodeApogee(rs.getString("COD_APO"));
                ue.setLibelleCourt(rs.getString("LIB_COURT"));
                ue.setType(TypeElement.UE);

                listUE.add(ue);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return listUE;
        
    }

    public List<Ec> getAllEcByParentId(int parentID, int grandParentID) {
        List<Ec> listEc = new ArrayList<Ec>();
        Oracle.logIn();

        ResultSet rs = Oracle.doSelectRequest("select e.*, l.fnum_listeec, ap.cod_apo from EC e, ec_ue_listeec l, apogee_corr_elp ap "
                + "where ap.TABLE_SOURCE = 'EC' and ap.CODE_PARCOURS = " + grandParentID + " and e.ID_EC = ap.id_source"
                + " and ap.id_source in (select FID_EC from EC_UE_LISTEEC where FID_UE = " + parentID + ") and l.fid_ec = e.id_ec"
                + " and l.fid_ue = " + parentID + " order by l.fnum_listeec");
        ResultSet rs2 = null;
        try {
            while (rs.next()) {
                Ec ec = new Ec();
                ec.setId(rs.getInt("ID_EC"));
                ec.setParentID(parentID);
                ec.setLibelleComplet(rs.getString("LIBELLE_EC"));
                ec.setFidResponsable(rs.getInt("FID_RESPONSABLE"));
                if (ec.getFidResponsable() != 0) {
                    rs2 = Oracle.doSelectRequest("select NOM, PRENOM from PERSONNEL where ID_PERS=" + ec.getFidResponsable());
                    while (rs2.next()) {
                        ec.setNomResponsable(rs2.getString("NOM"));
                        ec.setPrenomResponsable(rs2.getString("PRENOM"));
                    }
                }
                ec.setNbCredit(rs.getFloat("NB_CREDIT"));
                ec.setVolumeHeureCm(rs.getInt("VOLUME_HEURE_CM"));
                ec.setVolumeHeureTd(rs.getInt("VOLUME_HEURE_TD"));
                ec.setVolumeHeureTp(rs.getInt("VOLUME_HEURE_TP"));
                ec.setVolumeHeureStage(rs.getInt("VOLUME_HEURE_STAGE"));
                ec.setVolumeHeurMemoire(rs.getInt("VOLUME_HEURE_MEMOIRE"));
                ec.setNumListeEc(rs.getInt("FNUM_LISTEEC"));
                ec.setCodeApogee(rs.getString("COD_APO"));
                ec.setType(TypeElement.EC);
                
                //récupération de la description
                ResultSet rs3 = Oracle.doSelectRequest("select * from contribution_contenu where fid_ec=" + ec.getId());
                while (rs3.next()) {
                    ec.setContribution(rs3.getString("LIBELLE_CONTENU"));
                }
                listEc.add(ec);
            }
            } catch (SQLException ex) {
                Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
            }
        Oracle.logOut();
        return listEc;
    }

    public List<Ec> getAllEcInfoByParentId(int parentID, int grandParentID)  {
        List<Ec> listEc = new ArrayList<Ec>();
        Oracle.logIn();
        
        ResultSet rs = Oracle.doSelectRequest("select DISTINCT ec.*, apo.cod_apo from EC ec, apogee_corr_elp apo "
                + "where ID_EC in (select ID_SOURCE from apogee_corr_elp "
                + "where TABLE_SOURCE = 'EC' and CODE_PARCOURS = "+ grandParentID 
                + " and ID_SOURCE in (select FID_EC from EC_UE_LISTEEC where FID_UE = " + parentID + ")) "
                + "and apo.id_source = ec.id_ec and  TABLE_SOURCE = 'EC'");
        try {
            while (rs.next()) {
                Ec ec = new Ec();
                ec.setId(rs.getInt("ID_EC"));
                ec.setParentID(parentID);
                ec.setLibelleComplet(rs.getString("LIBELLE_EC"));
                ec.setType(TypeElement.EC);

                ec.setFidResponsable(rs.getInt("FID_RESPONSABLE"));
                ec.setNbCredit(rs.getFloat("NB_CREDIT"));
                ec.setVolumeHeureCm(rs.getInt("VOLUME_HEURE_CM"));
                ec.setVolumeHeureTd(rs.getInt("VOLUME_HEURE_TD"));
                ec.setVolumeHeureTp(rs.getInt("VOLUME_HEURE_TP"));
                ec.setVolumeHeureStage(rs.getInt("VOLUME_HEURE_STAGE"));
                ec.setVolumeHeurMemoire(rs.getInt("VOLUME_HEURE_MEMOIRE"));
                ec.setVolumeHeurePerso(rs.getInt("VOLUME_HEURE_PERSO"));
                ec.setCodeApogee(rs.getString("COD_APO"));
                
                ResultSet rs2 = null;
                ec.setFidResponsable(rs.getInt("FID_RESPONSABLE"));
                if (ec.getFidResponsable() != 0) {
                    rs2 = Oracle.doSelectRequest("select NOM, PRENOM from PERSONNEL where ID_PERS=" + ec.getFidResponsable());
                    while (rs2.next()) {
                        ec.setNomResponsable(rs2.getString("NOM"));
                        ec.setPrenomResponsable(rs2.getString("PRENOM"));
                    }
                }

                listEc.add(ec);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }

        Oracle.logOut();
        return listEc;

    }
    
    public Ec getEcById(String codeApogee){
        Ec ec = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from EC where ID_EC in (select ID_SOURCE from apogee_corr_elp where cod_apo = '"+codeApogee+"' and table_source = 'EC')");
        try {
            while (rs.next()) {
                ec = new Ec();
                ec.setId(rs.getInt("ID_EC"));
                ec.setLibelleComplet(rs.getString("LIBELLE_EC"));
                ec.setType(TypeElement.EC);
                ec.setFidResponsable(rs.getInt("FID_RESPONSABLE"));
                ec.setNbCredit(rs.getFloat("NB_CREDIT"));
                ec.setVolumeHeureCm(rs.getInt("VOLUME_HEURE_CM"));
                ec.setVolumeHeureTd(rs.getInt("VOLUME_HEURE_TD"));
                ec.setVolumeHeureTp(rs.getInt("VOLUME_HEURE_TP"));
                ec.setVolumeHeureStage(rs.getInt("VOLUME_HEURE_STAGE"));
                ec.setVolumeHeurMemoire(rs.getInt("VOLUME_HEURE_MEMOIRE"));
                ec.setVolumeHeurePerso(rs.getInt("VOLUME_HEURE_PERSO"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return ec;
    }

    public List<Ec> getEcByParcourstIdAndLibelleEc(int parcoursID, String libelleEc) {
        Ec ec = null;
        List<Ec> listEC = new ArrayList<Ec>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from EC where ID_EC in "
                + "(SELECT ID_SOURCE from APOGEE_CORR_ELP WHERE CODE_PARCOURS = " + parcoursID + ""
                + " and TABLE_SOURCE = 'EC' "
                + "and (LIBELLE = '" + libelleEc + "' or LIB_LONG = '" + libelleEc + "'))");
        try {
            while (rs.next()) {
                ec = new Ec();
                ec.setId(rs.getInt("ID_EC"));
                ec.setLibelleComplet(rs.getString("LIBELLE_EC"));
                ec.setType(TypeElement.EC);
                ec.setFidResponsable(rs.getInt("FID_RESPONSABLE"));
                ec.setNbCredit(rs.getFloat("NB_CREDIT"));
                ec.setVolumeHeureCm(rs.getInt("VOLUME_HEURE_CM"));
                ec.setVolumeHeureTd(rs.getInt("VOLUME_HEURE_TD"));
                ec.setVolumeHeureTp(rs.getInt("VOLUME_HEURE_TP"));
                ec.setVolumeHeureStage(rs.getInt("VOLUME_HEURE_STAGE"));
                ec.setVolumeHeurMemoire(rs.getInt("VOLUME_HEURE_MEMOIRE"));
                ec.setVolumeHeurePerso(rs.getInt("VOLUME_HEURE_PERSO"));

                listEC.add(ec);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return listEC;
        
    }

    public List<Ec> getEcByParcourstIdAndApogeeEc(int parcoursID, String codeApogee)  {
        Ec ec = null;
        List<Ec> listEC = new ArrayList<Ec>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from EC where ID_EC in "
                + "(SELECT ID_SOURCE from APOGEE_CORR_ELP WHERE CODE_PARCOURS = " + parcoursID + ""
                + " and TABLE_SOURCE = 'EC' "
                + "and COD_APO = '" + codeApogee + "')");
        try {
            while (rs.next()) {
                ec = new Ec();
                ec.setId(rs.getInt("ID_EC"));
                ec.setLibelleComplet(rs.getString("LIBELLE_EC"));
                ec.setType(TypeElement.EC);
                ec.setFidResponsable(rs.getInt("FID_RESPONSABLE"));
                ec.setNbCredit(rs.getFloat("NB_CREDIT"));
                ec.setVolumeHeureCm(rs.getInt("VOLUME_HEURE_CM"));
                ec.setVolumeHeureTd(rs.getInt("VOLUME_HEURE_TD"));
                ec.setVolumeHeureTp(rs.getInt("VOLUME_HEURE_TP"));
                ec.setVolumeHeureStage(rs.getInt("VOLUME_HEURE_STAGE"));
                ec.setVolumeHeurMemoire(rs.getInt("VOLUME_HEURE_MEMOIRE"));
                ec.setVolumeHeurePerso(rs.getInt("VOLUME_HEURE_PERSO"));

                listEC.add(ec);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        
            return listEC;
       
    }

    public int getIdFromLibelle(String type, String value){
        int resultat = -1;
        String select = "";
        String from = "";
        String where = "";
        String valeur = value.replaceAll("[\"']", "''");
        if (type.equals(TypeElement.DOMAINE.getLabel())) {
            select = "ID_DOM";
            from = "DOMAINE";
            where = "LIBELLE_DOMAINE";
        } else if (type.equals(TypeElement.COMPOSANTE.getLabel())) {
            select = "ID_COMP";
            from = "COMPOSANTE";
            where = "LIBELLE_LONG";
        } else if (type.equals(TypeElement.MENTION.getLabel())) {
            select = "ID_MEN";
            from = "MENTION";
            where = "LIBELLE_MENTION";
        } else if (type.equals(TypeElement.DIPLOME.getLabel())) {
            select = "ID_DIP";
            from = "DIPLOME";
            where = "FID_TYPE_DIP <3 and LIBELLE_DIPLOME";
        } else if (type.equals(TypeElement.PARCOURS.getLabel())) {
            select = "ID_PARCOURS";
            from = "PARCOURS";
            where = "LIBELLE_PARCOURS";
        } else if (type.equals(TypeElement.UE.getLabel())) {
            select = "ID_UE";
            from = "UE";
            where = "LIBELLE_UE";
        } else if (type.equals(TypeElement.EC.getLabel())) {
            select = "ID_EC";
            from = "EC";
            where = "LIBELLE_EC";
        }
        Oracle.logIn();
        String sql = "select " + select + " from " + from + " where " + where + "='" + valeur + "'";
        ResultSet rs = Oracle.doSelectRequest(sql);
        try {
            while (rs.next()) {
                resultat = rs.getInt(select);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultat;
    }

    public List<String> getLibelleByTypeElement(String type)  {
        List<String> resultat = new ArrayList<String>();
        String select = "";
        String from = "";
        String where = "";
        if (type.equals(TypeElement.DOMAINE.getLabel())) {
            select = "LIBELLE_DOMAINE";
            from = "DOMAINE";
            
        } else if (type.equals(TypeElement.COMPOSANTE.getLabel())) {
            select = "LIBELLE_LONG";
            from = "COMPOSANTE";
            
        } else if (type.equals(TypeElement.MENTION.getLabel())) {
            select = "LIBELLE_MENTION";
            from = "MENTION";
            
        } else if (type.equals(TypeElement.DIPLOME.getLabel())) {
            select = "LIBELLE_DIPLOME";
            from = "DIPLOME";
            where = "Where FID_TYPE_DIP <= 2 and OUVERT=1 AND id_dip in (select code_dip from apogee_liaison_parcours)";
        } else if (type.equals(TypeElement.PARCOURS.getLabel())) {
            select = "LIBELLE_PARCOURS";
            from = "PARCOURS";
            
        } else if (type.equals(TypeElement.UE.getLabel())) {
            select = "LIBELLE_UE";
            from = "UE";
            
        } else if (type.equals(TypeElement.EC.getLabel())) {
            select = "LIBELLE_EC";
            from = "EC";
        }
        Oracle.logIn();
        String requete = "select " + select + " from " + from + " " + where;
        ResultSet rs = Oracle.doSelectRequest(requete);
        try {
            while (rs.next()) {
                resultat.add(rs.getString(select));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultat;
    }

    public int getMinimumListeEc(int parentId, int listeEc) {
        int resultat = -1;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select MINIMUM from LISTEEC where FID_UE=" + parentId + " and NUM_LISTEEC=" + listeEc);
        try {
            while (rs.next()) {
                resultat = rs.getInt("MINIMUM");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultat;
    }
    
    public int getMinimumEcDansUE(int idUe) {
        int resultat = -1;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from ec_ue_listeec where FID_UE = "+idUe);
        
        try {
            while (rs.next()) {
                resultat = rs.getInt("MINIMUM");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultat;
    }

    //Fonction de récupération des informations en fonction du code apogée
    public Diplome getDiplomeByApogee(String codeApogee)  {
        Diplome diplome = null;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from DIPLOME where id_dip in (select code_dip from apogee_liaison_parcours where code_dip_apo = '"+codeApogee+"') and fid_type_dip < 3 and ouvert = 1");
        try {
            while (rs.next()){
              diplome = new Diplome();
              diplome.setId(rs.getInt("ID_DIP"));
              diplome.setCodeApogee(codeApogee);
              diplome.setFidComposante(rs.getInt("FID_COMP"));
              diplome.setFidMention(rs.getInt("FID_MEN"));
              diplome.setFidResponsable(rs.getInt("FID_RESP"));
              diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
              diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
              diplome.setEffectif(rs.getInt("EFFECTIF"));
              diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
              diplome.setDateCreation(rs.getDate("D_CREATION"));
              diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
              diplome.setType(TypeElement.DIPLOME);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return diplome;
    }

    public List<Diplome> getDiplomeByApogeeUE(String codeApogee)  {
        List<Diplome> liste = new ArrayList<Diplome>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from diplome where id_dip in "
                + "(select fid_diplome from specialite where id_specialite in "
                + "(select fid_specialite from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP where cod_apo = '"+codeApogee+"' and TABLE_SOURCE = 'UE'))) and FID_TYPE_DIP < 3 and OUVERT=1");
        try {
            while (rs.next()){
              Diplome diplome = new Diplome();
              diplome.setId(rs.getInt("ID_DIP"));
              diplome.setCodeApogee(null);
              diplome.setFidComposante(rs.getInt("FID_COMP"));
              diplome.setFidMention(rs.getInt("FID_MEN"));
              diplome.setFidResponsable(rs.getInt("FID_RESP"));
              diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
              diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
              diplome.setEffectif(rs.getInt("EFFECTIF"));
              diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
              diplome.setDateCreation(rs.getDate("D_CREATION"));
              diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
              diplome.setType(TypeElement.DIPLOME);
              
              liste.add(diplome);
          }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
      Oracle.logOut();
         return liste;
     }
    
    public List<Diplome> getDiplomeByApogeeEC(String codeApogee) {
         List<Diplome> liste = null;
         Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from diplome where id_dip in "
                + "(select fid_diplome from specialite where id_specialite in "
                + "(select fid_specialite from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP where cod_apo = '" + codeApogee + "' and TABLE_SOURCE = 'EC'))) and OUVERT=1");
        try {
            while (rs.next()) {
                if (liste == null) {
                    liste = new ArrayList<Diplome>();
                }
                Diplome diplome = new Diplome();
                diplome.setId(rs.getInt("ID_DIP"));
                diplome.setCodeApogee(null);
                diplome.setFidComposante(rs.getInt("FID_COMP"));
                diplome.setFidMention(rs.getInt("FID_MEN"));
                diplome.setFidResponsable(rs.getInt("FID_RESP"));
                diplome.setFidTypeDiplome(rs.getInt("FID_TYPE_DIP"));
                diplome.setCodeLmd2(rs.getString("CODE_LMD2"));
                diplome.setEffectif(rs.getInt("EFFECTIF"));
                diplome.setLibelleComplet(rs.getString("LIBELLE_DIPLOME"));
                diplome.setDateCreation(rs.getDate("D_CREATION"));
                diplome.setDateCreation(rs.getDate("D_MODIFICATION"));
                diplome.setType(TypeElement.DIPLOME);

                liste.add(diplome);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        Oracle.logOut();
        return liste;
    }

    public List<Parcours> getParcourByDiplomeApogee(String codeApogeeDiplome)  {
        Parcours parcours = null;
        List<Parcours> listParcours = new ArrayList<Parcours>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select * from PARCOURS where id_parcours in (select code_parcours from apogee_liaison_parcours where code_dip_apo = '" + codeApogeeDiplome + "')");
        try {
            while (rs.next()) {
                parcours = new Parcours();
                parcours.setId(rs.getInt("ID_PARCOURS"));
                parcours.setFidSpecialite(rs.getInt("FID_SPECIALITE"));
                parcours.setLibelleComplet(rs.getString("LIBELLE_PARCOURS"));
                parcours.setType(TypeElement.PARCOURS);
                listParcours.add(parcours);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listParcours;
    }

    public String getLibelleEC(int idEC)  {
        String resultat = "";
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select LIBElLE_CONTENU from CONTRIBUTION_CONTENU where FID_EC ='" + idEC + "'");
        try {
            while (rs.next()) {
                resultat = rs.getString("LIBElLE_CONTENU");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultat;
    }

    public int getIdDomaineByApogee(String codeApogee)  {
        int idDomaine = -1;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT ID_DOM FROM DOMAINE WHERE CODE_APOGEE='" + codeApogee + "'");
        try {
            while (rs.next()) {
                idDomaine = rs.getInt("ID_DOM");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idDomaine;
    }

    public int getIdMentionByApogee(String codeApogee) {
        int idMention = -1;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("SELECT ID_MEN FROM MENTION WHERE CODE_APOGEE_LMD2='" + codeApogee + "'");
        try {
            while (rs.next()) {
                idMention = rs.getInt("ID_MEN");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idMention;
    }

    public int getIdDiplomeByApogee(String codeApogee) {
        int idDiplome = -1;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select id_dip from DIPLOME where id_dip in "
                + "(select code_dip from apogee_liaison_parcours where code_dip_apo ='" + codeApogee + "')");
        try {
            while (rs.next()) {
                idDiplome = rs.getInt("id_dip");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idDiplome;
    }

    public int getIdParcoursByApogee(String codeApogee)  {
        int idParcours = -1;
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select id_parcours from PARCOURS where id_parcours in "
                + "(select code_parcours from apogee_liaison_parcours where code_vet_apo ='" + codeApogee + "')");
        try {
            while (rs.next()) {
                idParcours = rs.getInt("id_parcours");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }

        return idParcours;
    }

    public List<Integer> getAllParcoursByLibelleUE(String libelleUe) {
        List<Integer> allId = new ArrayList<Integer>();
        Oracle.logIn();
        
        ResultSet rs = Oracle.doSelectRequest("select  p.id_parcours from Semestre_ue s, ue u, parcours p "
                + "where libelle_ue = '" + libelleUe + "' "
                + "and u.id_ue = s.fid_ue "
                + "and s.fid_parcours = p.id_parcours "
                + "and p.id_parcours in (select code_parcours from APOGEE_CORR_ELP where code_parcours = p.id_parcours )");
        try {
            while (rs.next()) {
                allId.add(rs.getInt("id_parcours"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return allId;
    }
    
    public List<Integer> getAllParcoursByLibelleEC(String libelleEc) {
        List<Integer> allId = new ArrayList<Integer>();
        Oracle.logIn();
    
        ResultSet rs = Oracle.doSelectRequest(" select * from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP "
                + "where libelle = '"+ libelleEc +"' and TABLE_SOURCE = 'EC')");
        try {
            while (rs.next()) {
                allId.add(rs.getInt("id_parcours"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return allId;
    }

    public List<Integer> getAllParcoursByApogeeUE(String apogeeUe) {
        List<Integer> allId = new ArrayList<Integer>();
        Oracle.logIn();
        
        ResultSet rs = Oracle.doSelectRequest("select id_parcours from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP where cod_apo = '" + apogeeUe + "' and TABLE_SOURCE = 'UE')");
        try {
            while (rs.next()) {
                allId.add(rs.getInt("id_parcours"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }

        return allId;
    }

    public List<Integer> getAllParcoursByApogeeEC(String apogeeEc) {
        List<Integer> allId = new ArrayList<Integer>();
        Oracle.logIn();
        ResultSet rs = Oracle.doSelectRequest("select id_parcours from parcours where id_parcours in "
                + "(select code_parcours from APOGEE_CORR_ELP where cod_apo = '" + apogeeEc + "' and TABLE_SOURCE = 'EC')");
        try {
            while (rs.next()) {
                allId.add(rs.getInt("id_parcours"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(BDInteraction.class.getName()).log(Level.SEVERE, null, ex);
        }

        return allId;
    }
}